package com.apes.fn.rpt.settle.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.DateUtil;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author pengyb
 */
@Component
public class SummaryDao extends ApesDao {
    @Autowired
    EntityManager entityManager;


    /***
     * sSQL包装层，用于规范SQL，方便用于拼接
     */

    private String contactSQL(String sSQL, JSONObject param) {

        String contactSQL = "SELECT TO_CHAR(X.CREATE_DATE, 'YYYY-MM-DD') STATISTICS_DATE, X.*  FROM (" + sSQL + ") X WHERE 1 = 1";

        contactSQL += param.getString("SQL");
        contactSQL += "      ORDER BY STATISTICS_DATE ASC";
        return contactSQL;
    }


    /**
     * 查询结果格式
     *
     * @param dateStart
     * @param dateEnd
     * @param sb
     * @return
     */
    private List<Map> getResult(Date dateStart, Date dateEnd, StringBuffer sb) {
        Query query = entityManager.createNativeQuery(sb.toString());
        query.setParameter("DATE_END", DateUtil.format(dateEnd, DateUtil.SHOW_DATE_FORMAT));
        query.setParameter("DATE_START", DateUtil.format(dateStart, DateUtil.SHOW_DATE_FORMAT));
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }


    /**
     * 查询收入汇总
     *
     * @param dateStart
     * @param dateEnd
     * @return
     */
    public List<Map> queryIncome(Date dateStart, Date dateEnd) {
        //货币资金 应收账款 跨公司消费 取销售报表数据
        StringBuffer sb = new StringBuffer();
        sb.append("\n" +
                "SELECT COMPANY_ID,\n" +
                "       DATE_OF_STATISTICS,\n" +
                "       SUM(CASH)                   A,\n" +
                "       SUM(ACCOUNTS_RECEIVABLE)    B,\n" +
                "       SUM(ACCOUNTS_RECEIVABLEOUT) C,\n" +
                "       SUM(CROSS_COMPANY_INCOME)   D,\n" +
                "       SUM(CROSS_PAY)              CROSS_PAY,\n" +
                "       SUM(INTERNAL_DERATE)        INTERNAL_DERATE,\n" +
                "       SUM(DERATE)                 DERATE,\n" +
                "       SUM(LOSS_DERATE)            LOSS_DERATE,\n" +
                "       SUM(PLATFORM_SUBSIDY)       PLATFORM_SUBSIDY\n" +
                "FROM (\n" +
                "         SELECT COMPANY_ID,\n" +
                "                DATE_OF_STATISTICS,\n" +
                "                SUM(PAYMENT_MONEY)                                  CASH,\n" +
                "                SUM(ACCOUNTS_RECEIVABLEIN)  ACCOUNTS_RECEIVABLE,\n" +
                "                SUM(ACCOUNTS_RECEIVABLEOUT)                     ACCOUNTS_RECEIVABLEOUT,\n" +
                "                SUM(CROSS_COMPANY_INCOME)                           CROSS_COMPANY_INCOME,\n" +
                "                SUM(CROSS_COMPANY_PAY)                          CROSS_PAY,\n" +
                "                0                                                   INTERNAL_DERATE,\n" +
                "                0                                                   DERATE,\n" +
                "                0                                                   LOSS_DERATE," +
                "                0                                                   PLATFORM_SUBSIDY\n" +
                "\n" +
                "         FROM RCL_DAILYSALESREPORT R\n" +
                "         WHERE R.DATE_OF_STATISTICS >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "           AND R.DATE_OF_STATISTICS < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "         GROUP BY COMPANY_ID, DATE_OF_STATISTICS\n" +
                "         UNION ALL\n" +
                "         select COMPANY_ID,\n" +
                "                DATE_OF_STATISTICS,\n" +
                "                0                    CASH,\n" +
                "                0                    ACCOUNTS_RECEIVABLE,\n" +
                "                0                    ACCOUNTS_RECEIVABLEOUT,\n" +
                "                0                    CROSS_COMPANY_INCOME,\n" +
                "                0                    CROSS_PAY,\n" +
                "                SUM(INTERNAL_DERATE) INTERNAL_DERATE,\n" +
                "                SUM(DERATE)          DERATE,\n" +
                "                0                    LOSS_DERATE,\n" +
                "                0                    PLATFORM_SUBSIDY\n" +
                "         FROM (\n" +
                "                  SELECT COMPANY_ID,\n" +
                "                         TRUNC(A.CREATE_DATE)           DATE_OF_STATISTICS,\n" +
                "                         0                              CASH,\n" +
                "                         0                              ACCOUNTS_RECEIVABLE,\n" +
                "                         0                              ACCOUNTS_RECEIVABLEOUT,\n" +
                "                         0                              CROSS_COMPANY_INCOME,\n" +
                "                         0                              CROSS_PAY,\n" +
                "                         B.PLATFORM_INTERNAL_REDUCED    INTERNAL_DERATE,\n" +
                "                         B.PLATFORM_SERVICE_FEE_REDUCED DERATE,\n" +
                "                         0                              LOSS_DERATE,\n" +
                "                         0                              PLATFORM_SUBSIDY\n" +
                "                  FROM ACC_VOUCHER a,\n" +
                "                       ACC_VOUCHER_ITEM B\n" +
                "                  WHERE A.ID = B.VOUCHER_ID\n" +
                "                    AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "                    AND a.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss'\n" +
                "                    and A.CREATE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "                    AND A.CREATE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1)" +
                "                    GROUP BY COMPANY_ID, DATE_OF_STATISTICS" +
                "                   UNION ALL\n" +
                "                   select COMPANY_ID,\n" +
                "                          STATISTIC_DATE                 DATE_OF_STATISTICS,\n" +
                "                          0                              CASH,\n" +
                "                          0                              ACCOUNTS_RECEIVABLE,\n" +
                "                          0                              ACCOUNTS_RECEIVABLEOUT,\n" +
                "                          0                              CROSS_COMPANY_INCOME,\n" +
                "                          0                              CROSS_PAY,\n" +
                "                          0                              INTERNAL_DERATE,\n" +
                "                          0                              DERATE,\n" +
                "                          0                              LOSS_DERATE,\n" +
                "                          SUM(A.PLATFORM_COUPON_SUBSIDY) PLATFORM_SUBSIDY\n" +
                "                   FROM PLATFORM_FLOW A\n" +
                "                   WHERE A.FLOW_TYPE = 'platformSubsidy'\n" +
                "                     AND A.STATISTIC_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "                     AND A.STATISTIC_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "                     GROUP BY COMPANY_ID, STATISTIC_DATE)\n" +
                "GROUP BY COMPANY_ID, DATE_OF_STATISTICS");
        return getResult(dateStart, dateEnd, sb);

    }

    /**
     * 查询支出
     *
     * @param dateStart
     * @param dateEnd
     * @return
     */
    public List<Map> queryExpenditure(Date dateStart, Date dateEnd) {
        StringBuffer sb = new StringBuffer();
        sb.append("" +
                "   SELECT SUM(CONSIGN_MONEY)             B,\n" +
                "          SUM(OWN_MONEY)                 C,\n" +
                "          SUM(SUPPLY_CHAIN_FEE)          SUPPLY_CHAIN_FEE,\n" +
                "          SUM(DRAINAGE_COST)             DRAINAGE_COST,\n" +
                "          SUM(INSURANCE_FEE)             INSURANCE_FEE,\n" +
                "          SUM(PLATFORM_FEE)              A,\n" +
                "          SUM(QUOTATION_FEE)             QUOTATION_FEE,\n" +
                "          SUM(BORROW_MONEY)              E,\n" +
                "          SUM(ONLINE_PAY_FLOW)           ONLINE_PAY_FLOW,\n" +
                "          SUM(OFFLINE_COMPENSATION_FLOW) OFFLINE_COMPENSATION_FLOW," +
                "          SUM(CROSS_PAY)                 D,\n" +
                "          COMPANY_ID,\n" +
                "          TRUNC(CREATE_DATE)             DATE_OF_STATISTICS\n" +
                "   FROM (\n" +
                "            SELECT T.COMPANY_ID     COMPANY_ID,\n" +
                "                                                   T.CREATE_DATE AS CREATE_DATE,\n" +
                "                                                   0             AS CONSIGN_MONEY,\n" +
                "                                                   SUM(I.PRICE_SUBTOTAL) AS OWN_MONEY,\n" +
                "                                                   0             AS SUPPLY_CHAIN_FEE,\n" +
                "                                                   0             AS DRAINAGE_COST,\n" +
                "                                                   0             AS INSURANCE_FEE,\n" +
                "                                                   0             AS PLATFORM_FEE,\n" +
                "                                                   0             AS QUOTATION_FEE,\n" +
                "                                                   0             AS BORROW_MONEY,\n" +
                "                                                   0             AS ONLINE_PAY_FLOW,\n" +
                "                                                   0             AS OFFLINE_COMPENSATION_FLOW,\n" +
                "                                                   0             AS CROSS_PAY\n" +
                "                                             FROM  ACC_INVOICE T,ACC_INVOICE_ITEM I,MAS_PARTY_ROLE R\n" +
                "                                            WHERE T.PARTNER_ID = R.ID\n" +
                "                                            AND T.ID = I.INVOICE_ID\n" +
                "                                            AND R.ROLE_ID = 'Supplier'\n" +
                "                                            AND T.CREATE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "                                              AND T.CREATE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1 \n" +
                "                                              AND t.inventory_mode='IM_OWN' and t.PARTNER_ID<>'A00000' and t.partner_id<>'004465' \n" +
                "                                              group by T.COMPANY_ID,T.CREATE_DATE\n" +
                "                            UNION ALL\n" +
                "                            SELECT T.COMPANY_ID     COMPANY_ID,\n" +
                "                                   T.CREATE_DATE AS CREATE_DATE,\n" +
                "                                    SUM(I.PRICE_SUBTOTAL) AS CONSIGN_MONEY,\n" +
                "                                   0             AS OWN_MONEY,\n" +
                "                                   0             AS SUPPLY_CHAIN_FEE,\n" +
                "                                   0             AS DRAINAGE_COST,\n" +
                "                                   0             AS INSURANCE_FEE,\n" +
                "                                   0             AS PLATFORM_FEE,\n" +
                "                                   0             AS QUOTATION_FEE,\n" +
                "                                   0             AS BORROW_MONEY,\n" +
                "                                   0             AS ONLINE_PAY_FLOW,\n" +
                "                                   0             AS OFFLINE_COMPENSATION_FLOW,\n" +
                "                                   0             AS CROSS_PAY\n" +
                "                                             FROM  ACC_INVOICE T,ACC_INVOICE_ITEM I,MAS_PARTY_ROLE R\n" +
                "                                            WHERE T.PARTNER_ID = R.ID\n" +
                "                                            AND T.ID = I.INVOICE_ID\n" +
                "                                            AND R.ROLE_ID = 'Supplier'\n" +
                "                                            AND T.CREATE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "                                              AND T.CREATE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1 \n" +
                "                                              AND T.ORIGIN_BUSINESS_TYPE <> 'PriceChange' \n" +
                "                                              AND t.inventory_mode='IM_CONSIGN' and t.PARTNER_ID<>'A00000' and t.partner_id<>'004465' \n" +
                "                                              group by T.COMPANY_ID,T.CREATE_DATE" +
                "            UNION ALL\n" +
                "            SELECT T.COMPANY_ID                     COMPANY_ID,\n" +
                "                   T.CREATE_DATE                 AS CREATE_DATE,\n" +
                "                   0                             AS CONSIGN_MONEY,\n" +
                "                   0                             AS OWN_MONEY,\n" +
                "                   I.SUPPLY_CHAIN_MANAGEMENT_FEE AS SUPPLY_CHAIN_FEE,\n" +
                "                   0                             AS DRAINAGE_COST,\n" +
                "                   0                             AS INSURANCE_FEE,\n" +
                "                   0                             AS PLATFORM_FEE,\n" +
                "                   0                             AS QUOTATION_FEE,\n" +
                "                   0                             AS BORROW_MONEY,\n" +
                "                   0                             AS ONLINE_PAY_FLOW,\n" +
                "                   0                             AS OFFLINE_COMPENSATION_FLOW," +
                "                   0             AS CROSS_PAY\n" +
                "            FROM ACC_VOUCHER T\n" +
                "                     LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID\n" +
                "            WHERE T.CREATE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "              AND T.CREATE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "              AND T.VOUCHER_TYPE = 'SUPPLIER_CONSUME'\n" +
                "              AND (CLASSIFICATION not in ('05', '06') or CLASSIFICATION is null)\n" +
                "              AND (T.SCENE_ID <> 'PO002' AND T.SCENE_ID <> 'SR004' OR T.SCENE_ID IS NULL)\n" +
                "              AND T.INVENTORY_MODE\n" +
                "                = 'IM_CONSIGN'\n" +
                "              AND I.PRICE_TOTAL <> 0\n" +
                "   \n" +
                "            UNION ALL\n" +
                "            SELECT T.COMPANY_ID                     COMPANY_ID,\n" +
                "                   T.CREATE_DATE                 AS CREATE_DATE,\n" +
                "                   0                             AS CONSIGN_MONEY,\n" +
                "                   0                             AS OWN_MONEY,\n" +
                "                   I.SUPPLY_CHAIN_MANAGEMENT_FEE AS SUPPLY_CHAIN_FEE,\n" +
                "                   0                             AS DRAINAGE_COST,\n" +
                "                   0                             AS INSURANCE_FEE,\n" +
                "                   0                             AS PLATFORM_FEE,\n" +
                "                   0                             AS QUOTATION_FEE,\n" +
                "                   0                             AS BORROW_MONEY,\n" +
                "                   0                             AS ONLINE_PAY_FLOW,\n" +
                "                   0                             AS OFFLINE_COMPENSATION_FLOW," +
                "                   0             AS CROSS_PAY\n" +
                "            FROM ACC_VOUCHER T\n" +
                "                     LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID\n" +
                "            WHERE T.CREATE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "              AND T.CREATE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "              AND T.VOUCHER_TYPE = 'SUPPLIER_PURCHASE'\n" +
                "              AND (CLASSIFICATION not in ('05', '06') or CLASSIFICATION is null)\n" +
                "              AND (T.SCENE_ID <> 'PO002' AND T.SCENE_ID <> 'SR004' OR T.SCENE_ID IS NULL)\n" +
                "              AND T.INVENTORY_MODE\n" +
                "                = 'IM_OWN'\n" +
                "              AND I.PRICE_TOTAL <> 0\n" +
                "   \n" +
                "            UNION ALL\n" +
                "            SELECT T.COMPANY_ID                     COMPANY_ID,\n" +
                "                   T.CREATE_DATE                 AS CREATE_DATE,\n" +
                "                   0                             AS CONSIGN_MONEY,\n" +
                "                   0                             AS OWN_MONEY,\n" +
                "                   I.SUPPLY_CHAIN_MANAGEMENT_FEE AS SUPPLY_CHAIN_FEE,\n" +
                "                   0                             AS DRAINAGE_COST,\n" +
                "                   0                             AS INSURANCE_FEE,\n" +
                "                   0                             AS PLATFORM_FEE,\n" +
                "                   0                             AS QUOTATION_FEE,\n" +
                "                   0                             AS BORROW_MONEY,\n" +
                "                   0                             AS ONLINE_PAY_FLOW,\n" +
                "                   0                             AS OFFLINE_COMPENSATION_FLOW," +
                "                   0             AS CROSS_PAY\n" +
                "            FROM ACC_VOUCHER T\n" +
                "                     LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID\n" +
                "                     LEFT JOIN MAS_STORE D ON T.DEPT_ID = D.ID,\n" +
                "                 V_SERVE_TYPE S\n" +
                "            WHERE T.CREATE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "              AND T.CREATE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "              AND T.VOUCHER_TYPE\n" +
                "                = 'SUPPLIER_PURCHASE'\n" +
                "              AND T.SCENE_ID = 'SR004'\n" +
                "              AND T.INVENTORY_MODE = 'IM_OWN'\n" +
                "              AND I.PRICE_TOTAL\n" +
                "                <> 0\n" +
                "              AND D.STORE_TYPE_NAME = '001171'\n" +
                "              AND PARTNER_ID = 'A1001'\n" +
                "              AND S.PRODUCTID = I.PRODUCT_ID\n" +
                "              AND S.SERVE_TYPE = '007'\n" +
                "   \n" +
                "            UNION ALL\n" +
                "            SELECT L.COMPANY_ID            COMPANY_ID,\n" +
                "                   L.DATE_APPROVE       AS CREATE_DATE,\n" +
                "                   0                    AS CONSIGN_MONEY,\n" +
                "                   0                    AS OWN_MONEY,\n" +
                "                   0                    AS SUPPLY_CHAIN_FEE,\n" +
                "                   T.TMALL_PLATFORM_FEE AS DRAINAGE_COST,\n" +
                "                   0                    AS INSURANCE_FEE,\n" +
                "                   0                    AS PLATFORM_FEE,\n" +
                "                   0                    AS QUOTATION_FEE,\n" +
                "                   0                    AS BORROW_MONEY,\n" +
                "                   0                    AS ONLINE_PAY_FLOW,\n" +
                "                   0                    AS OFFLINE_COMPENSATION_FLOW," +
                "                   0             AS CROSS_PAY\n" +
                "            FROM Retail_Order_Payment_Method T\n" +
                "                     LEFT JOIN RETAIL_ORDER L ON T.RETAIL_ORDER_ID = L.ID\n" +
                "            WHERE L.CHANNEL_ID in ('100', '75')\n" +
                "              AND DATE_APPROVE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "              AND DATE_APPROVE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "   \n" +
                "            UNION ALL\n" +
                "            SELECT T.COMPANY_ID                COMPANY_ID,\n" +
                "                   T.CREATE_DATE            AS CREATE_DATE,\n" +
                "                   0                        AS CONSIGN_MONEY,\n" +
                "                   0                        AS OWN_MONEY,\n" +
                "                   0                        AS SUPPLY_CHAIN_FEE,\n" +
                "                   0                        AS DRAINAGE_COST,\n" +
                "                   T.CAR_INSURANCE_SALE_FEE AS INSURANCE_FEE,\n" +
                "                   0                        AS PLATFORM_FEE,\n" +
                "                   0                        AS QUOTATION_FEE,\n" +
                "                   0                        AS BORROW_MONEY,\n" +
                "                   0                        AS ONLINE_PAY_FLOW,\n" +
                "                   0                        AS OFFLINE_COMPENSATION_FLOW," +
                "                   0             AS CROSS_PAY\n" +
                "            FROM INSURANCE_ORDER T\n" +
                "            WHERE T.CREATE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "              AND T.CREATE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "   \n" +
                "            UNION ALL\n" +
                "            SELECT E.COMPANY_ID      COMPANY_ID,\n" +
                "            E.APPROVE_DATE AS CREATE_DATE,\n" +
                "            0              AS CONSIGN_MONEY,\n" +
                "            0              AS OWN_MONEY,\n" +
                "            0              AS SUPPLY_CHAIN_FEE,\n" +
                "            0              AS DRAINAGE_COST,\n" +
                "            0              AS INSURANCE_FEE,\n" +
                "            case when (select C.id from mas_party_role a, mas_role c where e.PARTNER_ID = a.id\n" +
                "             and a.ROLE_ID = c.id) = 'Supplier' then I.AMOUNT * -1 else I.AMOUNT\n" +
                "           end       AS PLATFORM_FEE,\n" +
                "            0              AS QUOTATION_FEE,\n" +
                "            0              AS BORROW_MONEY,\n" +
                "            0              AS ONLINE_PAY_FLOW,\n" +
                "            0              AS OFFLINE_COMPENSATION_FLOW,\n" +
                "            0             AS CROSS_PAY\n" +
                "            FROM PUR_INCOME_ITEM I\n" +
                "            LEFT JOIN PUR_INCOME E ON I.INCOME_ID = E.ID\n" +
                "            WHERE E.APPROVE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "              AND E.APPROVE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "            UNION ALL\n" +
                "            SELECT A.PAYMENT_ID                                  COMPANY_ID,\n" +
                "                   A.CREATE_DATE AS                              CREATE_DATE,\n" +
                "                   0             AS                              CONSIGN_MONEY,\n" +
                "                   0             AS                              OWN_MONEY,\n" +
                "                   0             AS                              SUPPLY_CHAIN_FEE,\n" +
                "                   0             AS                              DRAINAGE_COST,\n" +
                "                   0             AS                              INSURANCE_FEE,\n" +
                "                   0             AS                              PLATFORM_FEE,\n" +
                "                   CASE\n" +
                "                       WHEN A.TYPE = 'out' THEN\n" +
                "                           A.AMOUNT\n" +
                "                       WHEN A.TYPE = 'in' THEN A.AMOUNT * -1 END QUOTATION_FEE,\n" +
                "                   0             AS                              BORROW_MONEY,\n" +
                "                   0             AS                              ONLINE_PAY_FLOW,\n" +
                "                   0             AS                              OFFLINE_COMPENSATION_FLOW," +
                "                   0             AS CROSS_PAY\n" +
                "            FROM FN_SETTLE_EXPENSE A\n" +
                "            where ((A.TYPE = 'out' AND A.EXPENSE_TYPE != 'platformExpense') or (A.TYPE = 'in' AND A.EXPENSE_TYPE\n" +
                "                = 'platformExpense'))\n" +
                "              AND A.CREATE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "              AND A.CREATE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "   \n" +
                "            UNION ALL\n" +
                "            SELECT A.FN_CUSTOMER_ID  COMPANY_ID,\n" +
                "                   A.APPROVE_DATE AS CREATE_DATE,\n" +
                "                   0              AS CONSIGN_MONEY,\n" +
                "                   0              AS OWN_MONEY,\n" +
                "                   0              AS SUPPLY_CHAIN_FEE,\n" +
                "                   0              AS DRAINAGE_COST,\n" +
                "                   0              AS INSURANCE_FEE,\n" +
                "                   0              AS PLATFORM_FEE,\n" +
                "                   0              AS QUOTATION_FEE,\n" +
                "                   A.CAPITAL_BALANCE  AS BORROW_MONEY,\n" +
                "                   0              AS ONLINE_PAY_FLOW,\n" +
                "                   0              AS OFFLINE_COMPENSATION_FLOW," +
                "                   0             AS CROSS_PAY\n" +
                "            FROM FN_BUSINESS_DEBIT_NOTE A\n" +
                "            where A.state in( 'pending' ,'arrangement','pay','paid')\n" +
                "              AND A.APPROVE_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "              AND A.APPROVE_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "            UNION ALL\n" +
                "            SELECT A.COMPANY_ID                      COMPANY_ID,\n" +
                "                   A.STATISTIC_DATE               AS CREATE_DATE,\n" +
                "                   0                              AS CONSIGN_MONEY,\n" +
                "                   0                              AS OWN_MONEY,\n" +
                "                   0                              AS SUPPLY_CHAIN_FEE,\n" +
                "                   0                              AS DRAINAGE_COST,\n" +
                "                   0                              AS INSURANCE_FEE,\n" +
                "                   0                              AS PLATFORM_FEE,\n" +
                "                   0                              AS QUOTATION_FEE,\n" +
                "                   0                              AS BORROW_MONEY,\n" +
                "                   ONLINE_PAY_FLOW           AS ONLINE_PAY_FLOW,\n" +
                "                   OFFLINE_COMPENSATION_FLOW AS OFFLINE_COMPENSATION_FLOW," +
                "                   0             AS CROSS_PAY\n" +
                "            FROM PLATFORM_FLOW A\n" +
                "            where FLOW_TYPE in ('onlinePayFlow', 'offlineCompensationFlow')\n" +
                "              AND A.STATISTIC_DATE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "              AND A.STATISTIC_DATE < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "           UNION ALL\n" +
                "                            SELECT R.COMPANY_ID                      COMPANY_ID,\n" +
                "                                   R.DATE_OF_STATISTICS  AS CREATE_DATE,\n" +
                "                                   0                              AS CONSIGN_MONEY,\n" +
                "                                   0                              AS OWN_MONEY,\n" +
                "                                   0                              AS SUPPLY_CHAIN_FEE,\n" +
                "                                   0                              AS DRAINAGE_COST,\n" +
                "                                   0                              AS INSURANCE_FEE,\n" +
                "                                   0                              AS PLATFORM_FEE,\n" +
                "                                   0                              AS QUOTATION_FEE,\n" +
                "                                   0                              AS BORROW_MONEY,\n" +
                "                                   0                              AS ONLINE_PAY_FLOW,\n" +
                "                                   0                              AS OFFLINE_COMPENSATION_FLOW,\n" +
                "                                   SUM(CROSS_COMPANY_PAY) * -1 AS CROSS_PAY\n" +
                "                         FROM RCL_DAILYSALESREPORT R\n" +
                "                           WHERE R.DATE_OF_STATISTICS >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "                           AND R.DATE_OF_STATISTICS < to_date(:DATE_END, 'yyyy-MM-dd') + 1\n" +
                "                         GROUP BY R.CROSS_COMPANY_PAY,R.COMPANY_ID ,R.DATE_OF_STATISTICS" +
                "        )\n" +
                "   group by COMPANY_ID, TRUNC(CREATE_DATE)" +
                "   ");
        return getResult(dateStart, dateEnd, sb);
    }


    /**
     * 查询销售数据
     *
     * @param param
     * @return
     */
    public JSONArray querySale(JSONObject param) {
        String sSQL = "SELECT COMPANY_ID,\n" +
                "       (SELECT C.NAME\n" +
                "        FROM MAS_PARTY_ROLE B,\n" +
                "             MAS_PARTY C\n" +
                "        WHERE R.COMPANY_ID = B.ID\n" +
                "          AND B.PARTY_ID = C.ID)                              COMPANY_NAME,\n" +
                "       DEPT_ID,\n" +
                "       (SELECT D.NAME FROM MAS_DEPT D WHERE D.ID = R.DEPT_ID) DEPT_NAME,\n" +
                "       DATE_OF_STATISTICS                                     CREATE_DATE,\n" +
                "       PAYMENT_MONEY                                          PAYMENT_MONEY,\n" +
                "       CROSS_COMPANY_PAY                                      CROSS_COMPANY_PAY,\n" +
                "       CROSS_COMPANY_INCOME                                   CROSS_COMPANY_INCOME,\n" +
                "       ACCOUNTS_RECEIVABLEIN                                  ACCOUNTS_RECEIVABLEIN,\n" +
                "       ACCOUNTS_RECEIVABLEOUT                                 ACCOUNTS_RECEIVABLEOUT\n" +
                "\n" +
                "FROM RCL_DAILYSALESREPORT R " +
                " WHERE 1=1";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }


    /**
     * 三方平台引流成本明细
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findDrainageCost(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getChannelSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("merchantId", merchantId);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("end", page * size);
        query.setParameter("start", (page - 1) * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return (List<Map>) query.getResultList();
    }

    /**
     * 三方平台引流成本明细的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findDrainageCostCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getChannelSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("merchantId", merchantId);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查询渠道平台费明细SQL
     *
     * @return
     */
    private String getChannelSql() {
        return "SELECT  TO_CHAR(L.DATE_APPROVE, 'YYYY-MM-DD') AS DATE_APPROVE, " +
                "       L.ID         AS                                                              ID, " +
                "       L.COMPANY_ID AS                                                              COMPANY_ID, " +
                "       (SELECT C.NAME " +
                "        FROM MAS_PARTY C, " +
                "             MAS_PARTY_ROLE D " +
                "        WHERE C.ID = D.PARTY_ID " +
                "          AND D.ID = L.COMPANY_ID)                                                  COMPANY_NAME, " +
                "       (SELECT NAME FROM MAS_DEPT A WHERE A.ID = L.DEPT_ID)                         DEPT_NAME, " +
                "       (SELECT C.NAME FROM MAS_PAYMENT_METHOD C WHERE T.PAYMENT_METHOD_TYPE = C.ID) PAYMENT_METHOD_NAME, " +
                "       T.AMOUNT, " +
                "       T.TMALL_PLATFORM_FEE FEE " +
                "FROM Retail_Order_Payment_Method T " +
                "         LEFT JOIN RETAIL_ORDER L ON T.RETAIL_ORDER_ID = L.ID " +
                "WHERE l.CHANNEL_ID in ('100', '75') AND L.COMPANY_ID =:merchantId AND DATE_APPROVE>= :reconcileBeginDate AND DATE_APPROVE< :reconcileEndDate ORDER BY L.ID";
    }


    /**
     * 获取营业支出数据
     * @param companyId
     * @param dateStart
     * @param dateEnd
     * @return
     */
    public List<Map> findCostSummary(String companyId, Date dateStart, Date dateEnd) {
        String sb = "SELECT SUM(AMOUNT) AMOUNT, EXPENDITURE_TYPE\n" +
                "FROM REPORT_EXPENDITURE_SUMMARY\n" +
                "WHERE COMPANY_ID = :companyId AND STATISTICS_DATE >= to_date(:dateStart, 'yyyy-MM-dd') " +
                " AND STATISTICS_DATE< to_date(:dateEnd, 'yyyy-MM-dd') +1 " +
                " GROUP BY EXPENDITURE_TYPE";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("companyId", companyId);
        query.setParameter("dateEnd", DateUtil.format(dateEnd, DateUtil.SHOW_DATE_FORMAT));
        query.setParameter("dateStart", DateUtil.format(dateStart, DateUtil.SHOW_DATE_FORMAT));
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }

    /**
     * 获取营业收入数据
     * @param companyId
     * @param dateStart
     * @param dateEnd
     * @return
     */
    public List<Map> findIncomeSummary(String companyId, Date dateStart, Date dateEnd) {
        String sb = "SELECT SUM(SETTLEMENT_AMOUNT)       AMOUNT,\n" +
                "       SUM(PAYMENT_AMOUNT)          PAYMENT_AMOUNT,\n" +
                "       INCOME_TYPE," +
                "       (CASE WHEN INCOME_TYPE = 'cash' THEN \n" +
                "       (SELECT SUM(A.FREEZE_AMOUNT) FROM MAS_TO_PREPARE A WHERE A.PAYMENT_TYPE ='002035' AND A.COMPANY_ID=:companyId " +
                "       and a.COLLECT_MONEY_DATE >= to_date(:dateStart, 'yyyy-MM-dd') " +
                "       and a.COLLECT_MONEY_DATE < to_date(:dateEnd, 'yyyy-MM-dd') +1 )  \n" +
                "        WHEN INCOME_TYPE = 'receivableOut' THEN \n" +
                "       (SELECT SUM(A.FREEZE_AMOUNT) FROM MAS_TO_PREPARE A WHERE A.PAYMENT_TYPE ='002036' AND A.COMPANY_ID=:companyId " +
                "       and a.COLLECT_MONEY_DATE >= to_date(:dateStart, 'yyyy-MM-dd') " +
                "       and a.COLLECT_MONEY_DATE < to_date(:dateEnd, 'yyyy-MM-dd') +1 )  \n" +
                "       ELSE 0 END)PLATFORM_MANAGEMENT_FEE,\n" +
                "       (CASE WHEN INCOME_TYPE = 'cash' THEN \n" +
                "       (SELECT SUM(A.FREEZE_AMOUNT) FROM MAS_TO_PREPARE A WHERE A.PAYMENT_TYPE ='002035' AND A.COMPANY_ID=:companyId " +
                "       and a.COLLECT_MONEY_DATE < to_date(:dateStart, 'yyyy-MM-dd') )  \n" +
                "        WHEN INCOME_TYPE = 'receivableOut' THEN \n" +
                "       (SELECT SUM(A.FREEZE_AMOUNT) FROM MAS_TO_PREPARE A WHERE A.PAYMENT_TYPE ='002036' AND A.COMPANY_ID=:companyId " +
                "       and a.COLLECT_MONEY_DATE < to_date(:dateStart, 'yyyy-MM-dd') )  \n" +
                "       ELSE 0 END)PREVIOUS_SERVICE_FEE \n" +
                "FROM REPORT_INCOME_SUMMARY \n" +
                "WHERE INCOME_TYPE in ('receivableOut','cash','receivable','crossIncome','lossNotRefund') and " +
                "COMPANY_ID = :companyId AND STATISTICS_DATE >= to_date(:dateStart, 'yyyy-MM-dd') " +
                " AND STATISTICS_DATE< to_date(:dateEnd, 'yyyy-MM-dd') +1 " +
                " GROUP BY INCOME_TYPE order by INCOME_TYPE";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("companyId", companyId);
        query.setParameter("dateEnd", DateUtil.format(dateEnd, DateUtil.SHOW_DATE_FORMAT));
        query.setParameter("dateStart", DateUtil.format(dateStart, DateUtil.SHOW_DATE_FORMAT));
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }

    /**
     * 本期定损金额零售单金额
     * @param deptId
     * @param dateStart
     * @param dateEnd
     * @return
     *
     */
    public List<Map> findUrrentIssue(String deptId, Date dateEnd, Date dateStart) {
        String sb = " SELECT A.ID, A.POST_DATE, a.inner_business_type, sum(B.AMOUNT) amount,\n" +
                "       a.remark,\n" +
                "       (select car_license\n" +
                "           from fn_customer_expand b\n" +
                "          where a.customer_expand_id = b.id) CAR_LICENSE\n" +
                "  FROM RETAIL_ORDER A,retail_order_payment_method B  \n" +
                " where A.ID=B.retail_order_id AND a.claim_type is not null\n" +
                "   AND B.payment_method_type='09' and a.service_dept_id = :deptId\n" +
                "   and a.post_date >= to_date(:dateEnd,'yyyy-mm-dd')\n" +
                "   and a.post_date <  to_date(:dateStart,'yyyy-mm-dd')+1\n" +
                " group by A.POST_DATE, A.ID, a.inner_business_type, a.remark,\n" +
                "          a.customer_expand_id\n" +
                "UNION ALL \n" +
                "select a.ID, a.DATE_APPROVE POST_DATE, a.inner_business_type,\n" +
                "       sum(a.ADJUST_AMOUNT) amount, a.remark,\n" +
                "       (SELECT C.CAR_LICENSE\n" +
                "           FROM FN_CUSTOMER_EXPAND C\n" +
                "          WHERE C.ID = A.CUSTOMER_EXPAND_ID) CAR_LICENSE\n" +
                "  from LOSS_SALE_ADJUST a, RETAIL_ORDER b\n" +
                " where a.RETAIL_ID = b.id and b.service_dept_id = :deptId\n" +
                "   and a.date_approve >=to_date(:dateEnd,'yyyy-mm-dd')\n" +
                "   and a.date_approve <  to_date(:dateStart,'yyyy-mm-dd')+1\n" +
                " group by a.id, a.inner_business_type, a.DATE_APPROVE, a.dept_id, a.remark, A.CUSTOMER_EXPAND_ID";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("deptId", deptId);
        query.setParameter("dateEnd", DateUtil.format(dateEnd, DateUtil.SHOW_DATE_FORMAT));
        query.setParameter("dateStart", DateUtil.format(dateStart, DateUtil.SHOW_DATE_FORMAT));
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }

    /**
     * 集群账户,监管账户
     * @param deptId
     * @param dateStart
     * @param dateEnd
     * @return
     *
     */
    public List<Map> findAccount(String deptId, Date dateEnd, Date dateStart) {
        String sb = "SELECT b.id id,c.loss_quotation,A.RECEIVED_DATE+0 POST_DATE,A.APPORTION_AMOUNT AMOUNT,\n" +
                "                a.apportion_order_type INNER_BUSINESS_TYPE,\n" +
                "                (select f.CAR_LICENSE from RETAIL_ORDER_EXTENSION f,RETAIL_ORDER_ITEM i where i.ID = f.retail_order_id and b.ID = I.SALE_ID) CAR_LICENSE,\n" +
                "                a.remark,a.adjustment_amount ADJUST_AMOUNT\n" +
                "                 FROM SER_RECEIVED_APPORTION A,RETAIL_ORDER B,SER_LOSS_RECEIVED_ITEM C\n" +
                "                WHERE A.APPORTION_ORDER=B.ID AND A.LOSS_RECEIVED_ITEM=C.ID AND C.ACCOUNT_TYPE IN ('002031','002032')\n" +
                "                AND A.RECEIVED_DATE  >= to_date(:dateEnd,'yyyy-mm-dd')  AND A.RECEIVED_DATE < to_date(:dateStart,'yyyy-mm-dd') +1 \n" +
                "                AND B.SERVICE_DEPT_ID=:deptId  ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("deptId", deptId);
        query.setParameter("dateEnd", DateUtil.format(dateEnd, DateUtil.SHOW_DATE_FORMAT));
        query.setParameter("dateStart", DateUtil.format(dateStart, DateUtil.SHOW_DATE_FORMAT));
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }

    /**
     * 获取营业收入数据
     * @param companyId
     * @param dateStart
     * @param dateEnd
     * @return
     */
    public List<Map> findSubjectBalance(String merchantId, String companyId ,String dateStart, Date dateEnd ) {
        String sb = "SELECT t.COMPANY_ID, C.CODE, C.NAME, -1 * SUM(T.YE + T.DEBIT - T.CREDIT) BALANCE\n" +
                "  FROM (SELECT A.COMPANY_ID, A.POST_DATE, B.ACC_OPERATING_CHART_ID, B.DEBIT,\n" +
                "  B.CREDIT, 0 YE, B.AUX  FROM GL_VOUCHER A, GL_VOUCHER_ITEM B WHERE A.ID = B.VOUCHER_ID UNION ALL\n" +
                "  SELECT A.COMPANY_ID, TO_DATE(TO_CHAR(A.PERIOD) || '01', 'YYYYMMDD') POST_DATE,\n" +
                "  A.ACC_OPERATING_CHART_ID, 0 DEBIT, 0 CREDIT,SUM(OPENING_BALANCE) YE, A.NAME AUX\n" +
                "  FROM GL_CHART_AUX A GROUP BY A.COMPANY_ID, A.PERIOD, A.ACC_OPERATING_CHART_ID, A.NAME) T,\n" +
                "  MAS_ACC_OPERATING_CHART C WHERE T.ACC_OPERATING_CHART_ID = C.ID AND T.COMPANY_ID = :companyId\n" +
                "  AND C.CODE IN ('22410501', '22411004', '22410500','12210109') AND (T.AUX LIKE '%G"+merchantId+"%' or T.AUX LIKE '%K"+merchantId+"%')\n" +
                "  AND T.POST_DATE >= to_date(:dateStart, 'yyyy-mm-dd') AND T.POST_DATE < to_date(:dateEnd, 'yyyy-mm-dd') + 1\n" +
                " GROUP BY C.NAME, t.COMPANY_ID, C.CODE\n";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("companyId", companyId);
        query.setParameter("dateEnd", DateUtil.format(dateEnd, DateUtil.SHOW_DATE_FORMAT));
        query.setParameter("dateStart",dateStart);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }
}
